"""
function:匹配标准部门，将原始需求部门与完整机构进行相似度匹配，生成映射表，后将映射数据匹配放入原始Excel
author:wenzhengxing
date:2025/08/28 14:18
version:final
"""

import pandas as pd
import tkinter as tk
from tkinter import filedialog, messagebox, scrolledtext
import os
from difflib import SequenceMatcher
import re
import time


# 固定配置 - 请根据您的实际Excel文件修改这些值
SOURCE_SHEET = "汇总台账"  # 源数据所在的sheet名  Sheet1
SOURCE_COL = "原始需求部门"  # 源数据中原始需求部门的列名  原始需求部门
TARGET_SHEET = "完整机构"  # 目标部门列表所在的sheet名
TARGET_COL = "完整机构"  # 目标部门列表中标准部门名称的列名
MAPPING_SHEET = "映射表"  # 映射表sheet名

# 需要去除的干扰词列表（可以根据实际情况添加）
INTERFERENCE_WORDS = ["包河区", "芜湖路街道", "街道", "平安", "大圩镇", "长丰县", "埇桥区", "砀城镇",
                      "周寨镇", "宣州区", "宁国市","西津街道","需要该报表的单位","新时代","实践所",
                      "执法大队","工作中心"]

# 权重字符列表（匹配到这些字符时提高权重）
WEIGHTED_CHARS = ["应","急","战","织","人","社","妇","住","建","政","法","商","务","总","工","健","康"
                  "残","疾","医","保","民","法","事","务","团","农","宣","生","态","科","林","体","利"
                  "教","育","审","计","武","卫","退","役","联"]


def clean_string(s):
    """清洗字符串，去除干扰词"""
    if pd.isna(s) or s == '':
        return ""

    s = str(s).strip()
    for word in INTERFERENCE_WORDS:
        s = s.replace(word, '')

    # 去除多余空格
    s = re.sub(r'\s+', ' ', s).strip()
    return s


def similar(a, b):
    """计算两个字符串的相似度"""
    return SequenceMatcher(None, a, b).ratio()


def calculate_weighted_char_ratio(input_dept, target_dept):
    """计算带权重的字符匹配率"""
    input_dept = str(input_dept)
    target_dept = str(target_dept)

    # 计算基础匹配率
    input_chars = set(input_dept)
    target_chars = set(target_dept)
    matched_chars = input_chars & target_chars
    base_ratio = len(matched_chars) / len(input_chars) if input_chars else 0

    # 计算权重字符匹配
    weighted_match = 0
    total_weighted = 0

    for char in WEIGHTED_CHARS:
        if char in input_dept:
            total_weighted += 1
            if char in target_dept:
                weighted_match += 1

    # 计算加权匹配率（权重字符匹配占70%，基础匹配占30%）
    if total_weighted > 0:
        weighted_ratio = weighted_match / total_weighted
        final_ratio = 0.7 * weighted_ratio + 0.3 * base_ratio
    else:
        final_ratio = base_ratio

    return final_ratio


def find_best_match(input_dept, dept_list):
    """在部门列表中查找最佳匹配，使用两种策略"""
    if pd.isna(input_dept) or input_dept == '':
        return ""

    # 清洗输入字符串
    cleaned_input = clean_string(input_dept)
    if not cleaned_input:
        return ""

    best_match = ""
    highest_similarity = 0

    # 策略1: 使用相似度匹配
    for dept in dept_list:
        if pd.isna(dept) or dept == '':
            continue
        dept = str(dept).strip()
        similarity = similar(cleaned_input, dept)
        if similarity > highest_similarity:
            highest_similarity = similarity
            best_match = dept

    # 设置相似度阈值
    if highest_similarity > 0.6:
        return best_match
    else:
        # 策略2: 相似度匹配失败时，使用基于字符的匹配
        return char_based_match(input_dept, dept_list)


def char_based_match(input_dept, dept_list):
    """基于字符的匹配策略：使用带权重的字符匹配"""
    if pd.isna(input_dept) or input_dept == '':
        return ""

    input_dept = clean_string(input_dept)
    if not input_dept:
        return ""

    best_match = ""
    max_weighted_ratio = 0
    highest_similarity = 0

    for dept in dept_list:
        if pd.isna(dept) or dept == '':
            continue

        dept = str(dept).strip()

        # 计算带权重的字符匹配率
        weighted_ratio = calculate_weighted_char_ratio(input_dept, dept)

        # 计算相似度
        similarity = similar(input_dept, dept)

        # 更新最佳匹配
        if weighted_ratio > max_weighted_ratio:
            max_weighted_ratio = weighted_ratio
            best_match = dept
            highest_similarity = similarity
        elif weighted_ratio == max_weighted_ratio and similarity > highest_similarity:
            best_match = dept
            highest_similarity = similarity

    # 设置字符匹配阈值（匹配率大于0.7）
    if max_weighted_ratio > 0.7:
        return best_match
    else:
        return "无匹配项"


def create_mapping_table(unique_departments, target_departments):
    """创建映射表"""
    mapping_table = []

    # 添加进度条
    progress_window = tk.Toplevel(root)
    progress_window.title("匹配进度")
    progress_window.geometry("400x100")

    progress_label = tk.Label(progress_window, text="正在创建映射表，请稍候...")
    progress_label.pack(pady=10)

    progress_bar = ttk.Progressbar(progress_window, orient=tk.HORIZONTAL,
                                   length=300, mode='determinate')
    progress_bar.pack(pady=10)
    progress_window.update()

    total = len(unique_departments)
    for i, dept in enumerate(unique_departments):
        if pd.isna(dept):
            mapping_table.append(("", "无匹配项"))
            continue

        best_match = find_best_match(dept, target_departments)
        mapping_table.append((dept, best_match))

        # 更新进度条
        progress_bar['value'] = (i + 1) / total * 100
        progress_label.config(text=f"处理中: {i + 1}/{total} ({progress_bar['value']:.1f}%)")
        progress_window.update()

    progress_window.destroy()
    return pd.DataFrame(mapping_table, columns=['原始部门', '匹配部门'])


def check_and_update_mapping(mapping_df, source_departments):
    """检查映射表是否需要更新，添加新的部门"""
    existing_departments = set(mapping_df['原始部门'])
    new_departments = set(source_departments) - existing_departments

    if len(new_departments) > 0:
        return messagebox.askyesno("映射表更新",
                                   f"发现 {len(new_departments)} 个新部门不在当前映射表中。\n是否要更新映射表以包含这些新部门？")
    return False


def process_excel():
    """处理Excel文件的主要函数"""
    file_path = file_entry.get()
    if not file_path:
        messagebox.showerror("错误", "请先选择Excel文件")
        return

    try:
        start_time = time.time()

        # 读取源数据和目标数据
        source_df = pd.read_excel(file_path, sheet_name=SOURCE_SHEET)
        target_df = pd.read_excel(file_path, sheet_name=TARGET_SHEET)

        # 检查列是否存在
        if SOURCE_COL not in source_df.columns:
            messagebox.showerror("错误", f"源sheet中不存在列: {SOURCE_COL}")
            return

        if TARGET_COL not in target_df.columns:
            messagebox.showerror("错误", f"目标sheet中不存在列: {TARGET_COL}")
            return

        # 获取目标部门列表
        dept_list = target_df[TARGET_COL].dropna().unique()

        # 获取原始部门去重列表
        unique_departments = source_df[SOURCE_COL].dropna().unique()

        # 检查是否存在映射表
        use_existing_mapping = False
        mapping_df = None

        try:
            # 尝试读取现有的映射表
            mapping_df = pd.read_excel(file_path, sheet_name=MAPPING_SHEET)

            # 检查映射表格式是否正确
            if '原始部门' in mapping_df.columns and '匹配部门' in mapping_df.columns:
                # 询问用户是否使用现有映射表
                use_existing_mapping = messagebox.askyesno(
                    "使用现有映射表",
                    "检测到已存在映射表。是否使用现有映射表而不重新生成？"
                )

                if use_existing_mapping:
                    # 检查映射表是否需要更新
                    need_update = check_and_update_mapping(mapping_df, unique_departments)

                    if need_update:
                        # 更新映射表，添加新的部门
                        existing_departments = set(mapping_df['原始部门'])
                        new_departments = [dept for dept in unique_departments if dept not in existing_departments]

                        if new_departments:
                            # 创建进度窗口
                            progress_window = tk.Toplevel(root)
                            progress_window.title("更新映射表")
                            progress_window.geometry("400x100")

                            progress_label = tk.Label(progress_window, text="正在更新映射表，请稍候...")
                            progress_label.pack(pady=10)

                            progress_bar = ttk.Progressbar(progress_window, orient=tk.HORIZONTAL,
                                                           length=300, mode='determinate')
                            progress_bar.pack(pady=10)
                            progress_window.update()

                            # 为新的部门创建映射
                            new_mappings = []
                            total = len(new_departments)
                            for i, dept in enumerate(new_departments):
                                best_match = find_best_match(dept, dept_list)
                                new_mappings.append((dept, best_match))

                                # 更新进度条
                                progress_bar['value'] = (i + 1) / total * 100
                                progress_label.config(text=f"处理中: {i + 1}/{total} ({progress_bar['value']:.1f}%)")
                                progress_window.update()

                            progress_window.destroy()

                            # 将新的映射添加到现有映射表
                            new_mapping_df = pd.DataFrame(new_mappings, columns=['原始部门', '匹配部门'])
                            mapping_df = pd.concat([mapping_df, new_mapping_df], ignore_index=True)
        except:
            # 如果读取映射表失败，忽略错误，继续创建新的映射表
            pass

        # 如果没有使用现有映射表或映射表不存在，则创建新的映射表
        if not use_existing_mapping or mapping_df is None:
            mapping_df = create_mapping_table(unique_departments, dept_list)

        # 将映射表转换为字典，提高查找效率
        mapping_dict = dict(zip(mapping_df['原始部门'], mapping_df['匹配部门']))

        # 应用映射表到源数据
        source_df['匹配部门'] = source_df[SOURCE_COL].map(mapping_dict)

        # 处理NaN值
        source_df['匹配部门'] = source_df['匹配部门'].fillna("无匹配项")

        # 统计匹配结果
        matched_count = (source_df['匹配部门'] != "无匹配项").sum()
        total_count = len(source_df)

        # 保存结果到新文件
        output_path = file_path.replace('.xlsx', '_匹配结果.xlsx').replace('.xls', '_匹配结果111.xls')

        # 使用ExcelWriter保存，以便保留原格式
        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            source_df.to_excel(writer, sheet_name=SOURCE_SHEET, index=False)
            target_df.to_excel(writer, sheet_name=TARGET_SHEET, index=False)
            mapping_df.to_excel(writer, sheet_name=MAPPING_SHEET, index=False)

        end_time = time.time()
        processing_time = end_time - start_time

        # 显示匹配详情
        match_details = f"处理完成！\n总记录数: {total_count}\n成功匹配: {matched_count}\n匹配率: {matched_count / total_count * 100:.2f}%\n处理时间: {processing_time:.2f}秒\n\n"

        # 显示未匹配的项目
        unmatched = source_df[source_df['匹配部门'] == "无匹配项"]
        if len(unmatched) > 0:
            match_details += f"未匹配的项目({len(unmatched)}个):\n"
            for i, row in unmatched.head(10).iterrows():  # 只显示前10个未匹配项
                match_details += f"  - {row[SOURCE_COL]}\n"
            if len(unmatched) > 10:
                match_details += f"  ... 还有{len(unmatched) - 10}个未显示\n"

        match_details += f"\n结果已保存到: {output_path}"

        # 创建一个可滚动的文本窗口显示详细信息
        show_detailed_result(match_details)

    except Exception as e:
        messagebox.showerror("错误", f"处理过程中出错: {str(e)}")


def show_detailed_result(message):
    """创建一个新窗口显示详细结果"""
    result_window = tk.Toplevel(root)
    result_window.title("匹配结果详情")
    result_window.geometry("600x400")

    # 创建文本框和滚动条
    text_frame = tk.Frame(result_window)
    text_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)

    text_widget = scrolledtext.ScrolledText(text_frame, wrap=tk.WORD)
    text_widget.pack(fill=tk.BOTH, expand=True)

    # 插入文本
    text_widget.insert(tk.END, message)
    text_widget.config(state=tk.DISABLED)  # 设置为只读

    # 关闭按钮
    close_btn = tk.Button(result_window, text="关闭", command=result_window.destroy)
    close_btn.pack(pady=10)


def browse_file():
    """打开文件选择对话框"""
    filename = filedialog.askopenfilename(
        title="选择Excel文件",
        filetypes=[("Excel files", "*.xlsx *.xls")]
    )
    if filename:
        file_entry.delete(0, tk.END)
        file_entry.insert(0, filename)


# 创建GUI界面
root = tk.Tk()
root.title("Excel部门匹配工具")
root.geometry("500x350")

# 导入ttk用于进度条
from tkinter import ttk

# 固定配置显示
config_frame = tk.Frame(root)
config_frame.pack(pady=10, padx=10, fill=tk.X)

tk.Label(config_frame, text="固定配置:", font=("Arial", 10, "bold")).grid(row=0, column=0, sticky=tk.W, columnspan=2)
tk.Label(config_frame, text=f"源数据Sheet: {SOURCE_SHEET}").grid(row=1, column=0, sticky=tk.W)
tk.Label(config_frame, text=f"源数据列名: {SOURCE_COL}").grid(row=2, column=0, sticky=tk.W)
tk.Label(config_frame, text=f"目标部门Sheet: {TARGET_SHEET}").grid(row=1, column=1, sticky=tk.W)
tk.Label(config_frame, text=f"目标部门列名: {TARGET_COL}").grid(row=2, column=1, sticky=tk.W)
tk.Label(config_frame, text=f"映射表Sheet: {MAPPING_SHEET}").grid(row=3, column=0, sticky=tk.W, columnspan=2)

# 干扰词显示
tk.Label(config_frame, text="干扰词过滤:", font=("Arial", 10, "bold")).grid(row=4, column=0, sticky=tk.W, columnspan=2)
tk.Label(config_frame, text=", ".join(INTERFERENCE_WORDS)).grid(row=5, column=0, sticky=tk.W, columnspan=2)

# 权重字符显示
tk.Label(config_frame, text="权重字符:", font=("Arial", 10, "bold")).grid(row=6, column=0, sticky=tk.W, columnspan=2)
tk.Label(config_frame, text=", ".join(WEIGHTED_CHARS)).grid(row=7, column=0, sticky=tk.W, columnspan=2)

# 新增功能说明
tk.Label(config_frame, text="功能说明:", font=("Arial", 10, "bold")).grid(row=8, column=0, sticky=tk.W, columnspan=2)
tk.Label(config_frame, text="如果映射表已存在，程序会询问是否使用现有映射表").grid(row=9, column=0, sticky=tk.W,
                                                                                  columnspan=2)
tk.Label(config_frame, text="如果源数据中有新部门，程序会询问是否更新映射表").grid(row=10, column=0, sticky=tk.W,
                                                                                  columnspan=2)

# 文件选择部分
file_frame = tk.Frame(root)
file_frame.pack(pady=10, padx=10, fill=tk.X)

tk.Label(file_frame, text="Excel文件:").grid(row=0, column=0, sticky=tk.W)
file_entry = tk.Entry(file_frame, width=40)
file_entry.grid(row=0, column=1, padx=5)
tk.Button(file_frame, text="浏览", command=browse_file).grid(row=0, column=2)

# 处理按钮
process_btn = tk.Button(root, text="开始匹配", command=process_excel)
process_btn.pack(pady=20)

root.mainloop()